package cn.acyou.demo23.repository;

import cn.acyou.demo23.entity.User;
import cn.acyou.demo23.entity.UserVo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

/**
 * 用户存储库
 *
 * @author youfang
 * @date 2021/06/23
 */
public interface UserRepository extends JpaRepository<User, Long> {

    @Query("select u from User u where userName = ?1 AND signature = ?2")
    @Transactional(timeout = 10)
    List<User> selectUser(String userName, String signature);

    @Query("select u from User u where userName = :userName AND signature = :signature")
    List<User> selectUser2(@Param("userName") String userName, @Param("signature") String signature);

    //??? 查不出role_name ???
    @Query(value = "SELECT su.*,sr.role_name FROM sys_user su LEFT JOIN sys_role sr on su.role_id = sr.id ", nativeQuery = true)
    List<User> selectUser3();

    //??? new ???
    @Query(value = "SELECT new cn.acyou.demo23.entity.UserVo(su.userId, su.roleId, su.userName, su.phone, sr.roleName) FROM User su LEFT JOIN Role sr on su.roleId = sr.id ")
    List<UserVo> selectUser4();

    //??? Map ???
    @Query(value = "SELECT su.*,sr.role_name FROM sys_user su LEFT JOIN sys_role sr on su.role_id = sr.id ", nativeQuery = true)
    List<Map<String, Object>> selectUser5();

    //??? Map ???
    @Query(value = "SELECT count(*) as count, IFNULL(age,1) as age FROM student GROUP BY age", nativeQuery = true)
    List<Map<String, Object>> selectUser6();

}
